﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Obj;


namespace DAL
{

    /// <summary>
    /// Summary description for SQLGaragem
    /// </summary>
    public class SQLGaragem : dataAccess
    {
        public SQLGaragem()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public bool Delete(int garagemID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_Delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool Update(objGaragem garagem)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_Update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.NVarChar).Value = garagem.Nome ;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = garagem.Descricao;
                cmd.Parameters.Add("@foto", SqlDbType.NVarChar).Value = garagem.Foto;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = garagem.Ativo; 
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagem.GaragemID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);

            }
        }

        public int Insert(objGaragem garagem)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_Insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.NVarChar).Value = garagem.Nome;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = garagem.Descricao;
                cmd.Parameters.Add("@foto", SqlDbType.NVarChar).Value = garagem.Foto;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = garagem.Ativo; 
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Direction = ParameterDirection.Output;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@garagemID"].Value;
            }
        }

        public List<objGaragem> SelectAll(string nome) 
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_selectByDescricao", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@nome", SqlDbType.NVarChar).Value = nome; 
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        
        }

        public List<objGaragem> SelectAll()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
        public List<objGaragem> SelectAllAtivas()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = true; 
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        public objGaragem SelectByID(int garagemID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("garagem_SelectByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }

        private objGaragem obj(IDataReader reader)
        {
            objGaragem garagem = new objGaragem(    (int)reader["GaragemID"],
                                                    reader["nome"].ToString(),
                                                     reader["descricao"].ToString(),
                                                    reader["foto"].ToString(),
                                                    (bool)reader["ativo"]
           
                                                );
            return garagem;
        }

        private List<objGaragem> objLista(IDataReader reader)
        {
            List<objGaragem> garagens = new List<objGaragem>();
            while (reader.Read())
                garagens.Add(obj(reader));
            return garagens;

        }

    }
}